package dao.impl;


import dao.RaceDao;
import model.Race;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class RaceDaoImpl implements RaceDao {
    /**
     * The Pstmt.
     */
    PreparedStatement pstmt = null;
    /**
     * The Rs.
     */
    ResultSet rs = null;

    @Override
    public int insert(Race race) {
        int ret = 0;
        String sql = "insert into race values(null,?,?,?,?)";
        Connection conn = BaseDao.getConnection();
        try {
            conn = BaseDao.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, race.getRname());
            pstmt.setLong(2, race.getCid());
            pstmt.setString(3, race.getRurl());
            pstmt.setString(4, race.getDesc());

            ret = pstmt.executeUpdate();
            //此处在这里不应该在这里调用close方法,否则可能会造成资源泄露
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            BaseDao.close(null, pstmt, conn);
        }
        return ret;
    }

    @Override
    public int update(Race race) {
        int ret = 0;
        String sql = "update race set rname=?,rurl=?,`desc`=? where cid=?";
        Connection conn = BaseDao.getConnection();
        try {
            conn = BaseDao.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, race.getRname());
            pstmt.setString(2, race.getRurl());
            pstmt.setString(3, race.getDesc());
            pstmt.setLong(4, race.getCid());

            ret = pstmt.executeUpdate();
            //此处在这里不应该在这里调用close方法,否则可能会造成资源泄露
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            BaseDao.close(null, pstmt, conn);
        }
        return ret;
    }

    @Override
    public Race getRaceById(int rid) {
        Race race = null;
        String sql = "SELECT * FROM race WHERE rid=?";
        Connection conn = BaseDao.getConnection();
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setLong(1, rid);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                race = new Race();
                race.setRid(rid);
                race.setRname(rs.getString("rname"));
                race.setCid(rs.getInt("cid"));
                race.setRurl(rs.getString("rurl"));
                race.setDesc(rs.getString("desc"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            BaseDao.close(rs, pstmt, conn);
        }
        return race;
    }

    @Override
    public List<Race> getAllRaces(int pageSize, int pageIndex) {
        List<Race> list = new ArrayList<Race>();
        String sql = "select * from race limit ? offset ?";
        Connection conn = BaseDao.getConnection();
        try {
            conn = BaseDao.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, pageSize);
            pstmt.setInt(2, pageSize * (pageIndex - 1));

            rs = pstmt.executeQuery();
            while (rs.next()) {
                list.add(new Race(rs.getInt("rid"),rs.getString("rname"), rs.getLong("cid"), rs.getString("rurl"), rs.getString("desc")));
            }
            //此处在这里不应该在这里调用close方法,否则可能会造成资源泄露
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            BaseDao.close(rs, pstmt, conn);
        }
        return list;
    }

    @Override
    public List<Race> getRacesByUser(int uid, int pageIndex,int pageSize) {
        List<Race> list = new ArrayList<Race>();
        String sql = "select * from race where rid in (select rid from team where tid in (select tid from team_user where uid=?)) limit ? offset ?";
        Connection conn = BaseDao.getConnection();
        try {
            conn = BaseDao.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, uid);
            pstmt.setInt(2, pageSize);
            pstmt.setInt(3, pageSize * (pageIndex - 1));

            rs = pstmt.executeQuery();
            while (rs.next()) {
                list.add(new Race(rs.getInt("rid"),rs.getString("rname"), rs.getLong("cid"), rs.getString("rurl"), rs.getString("desc")));
            }
            //此处在这里不应该在这里调用close方法,否则可能会造成资源泄露
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            BaseDao.close(rs, pstmt, conn);
        }
        return list;
    }

    @Override
    public int delete(int rid) {
        int ret = 0;
        String sql = "delete from race where rid=?";
        Connection conn = BaseDao.getConnection();
        try {
            conn = BaseDao.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, rid);

            ret = pstmt.executeUpdate();
            //此处在这里不应该在这里调用close方法,否则可能会造成资源泄露
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            BaseDao.close(null, pstmt, conn);
        }
        return ret;
    }

    @Override
    public List<Race> findByPartName(String partName, int pageIndex, int pageSize) {
        List<Race> list = new ArrayList<>();
        String sql = "SELECT * FROM race where rname like ? LIMIT ?,? ";
        Connection conn = BaseDao.getConnection();
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, "%" + partName + "%");
            pstmt.setInt(2, (pageIndex - 1) * pageSize);
            pstmt.setInt(3, pageSize);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                Race race = new Race();
                race.setRid(rs.getLong("rid"));
                race.setRname(rs.getString("rname"));
                race.setCid(rs.getLong("cid"));
                race.setRurl(rs.getString("rurl"));
                race.setDesc(rs.getString("desc"));
                list.add(race);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            BaseDao.close(rs, pstmt, conn);
        }
        return list;
    }

    @Override
    public int pageCount() {
        int count = 0;
        String sql = "SELECT count(*) from race";
        Connection conn = BaseDao.getConnection();
        try {
            pstmt = conn.prepareStatement(sql);
            ResultSet resultSet = pstmt.executeQuery();
            if (resultSet.next()) {
                count = resultSet.getInt(1);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            BaseDao.close(rs, pstmt, conn);
        }
        return count;
    }

    @Override
    public int countByUser(int uid) {
        int count = 0;
        String sql = "select count(*) from race where rid in (select rid from team where tid in (select tid from team_user where uid=?))";
        Connection conn = BaseDao.getConnection();
        try {

            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1,uid);
            ResultSet resultSet = pstmt.executeQuery();
            if (resultSet.next()) {
                count = resultSet.getInt(1);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            BaseDao.close(rs, pstmt, conn);
        }
        System.out.println(count);
        return count;
    }


    @Override
    public int countByName(String partName) {
        int count = 0;
        String sql = "SELECT count(*) FROM race where rname like ? ";
        Connection conn = BaseDao.getConnection();
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, "%" + partName + "%");
            rs = pstmt.executeQuery();
            while (rs.next()) {
                count = rs.getInt(1);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            BaseDao.close(rs, pstmt, conn);
        }
        return count;
    }


}
